package com.tim.helper.analysis.xssd;

import cn.hutool.core.collection.ListUtil;
import com.tim.MainHelper;
import com.tim.util.DbUtils;
import org.apache.commons.collections.CollectionUtils;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.LongAdder;

/**
 * 1，Zero date value prohibited。
 *  https://blog.csdn.net/yongbutingxide/article/details/89706635
 *  带参数：?zeroDateTimeBehavior=CONVERT_TO_NULL
 */
public class BackupDbHelper {
    // 懒猪 猪耳朵 每日说 贝小集 懒猪同城 懒猪探店
//    String project = "每日说";
    String project = "贝小集";
    int num = 1000;
    int sleepSec = 1;
    // 数据库信息；before方法中按project设置
    String username,password,dbname, ipPort;

    // 以下一般不改
    List<String> dbList;
    String excludes = "information_schema,mysql,test,sys,__recycle_bin__,performance_schema," +
            "test2,xqxc_report";
    String outputDir = MainHelper.OUTPUT_DIR + project;
    LongAdder totalTable = new LongAdder();
    LongAdder totalRecTable = new LongAdder();

    @Test
    public void backup() throws Exception {
        if(CollectionUtils.isEmpty(dbList)){
            throw new RuntimeException();
        }

        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+dbname+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        Statement stmt = connection.createStatement();
        String queryTable = "SELECT * FROM information_schema.TABLES t where t.TABLE_SCHEMA not in ('"
                +excludes.replaceAll(",", "','")+"') and engine is not null;";
        System.out.println(queryTable);
        ResultSet tableResultSet = stmt.executeQuery(queryTable);
        Map<String, List<String>> schemaTablesMap = new HashMap<>();
        while (tableResultSet.next()){
            String tableSchema = tableResultSet.getString("table_schema");
            String tableName = tableResultSet.getString("table_name");
            if(!schemaTablesMap.containsKey(tableSchema)){
                schemaTablesMap.put(tableSchema, new ArrayList<>());
            }
            schemaTablesMap.get(tableSchema).add(tableName);
        }
        tableResultSet.close();
        stmt.close();
        connection.close();

        ExecutorService executorService = Executors.newFixedThreadPool(Math.min(10, schemaTablesMap.size()));
        schemaTablesMap.forEach((k,v)->{
            String destDir = MainHelper.OUTPUT_DIR+"/"+v+"/";
            File dir = new File(destDir);
            if(!dir.exists()){
                dir.mkdirs();
            }
            ListUtil.split(v, 20).forEach(e-> {
                executorService.submit(()-> {
                    try {
                        handleDb(k,e);
                    } catch (Exception exception) {
                        exception.printStackTrace();
                    }
                });
            });
        });
        executorService.shutdown();
        executorService.awaitTermination(10, TimeUnit.MINUTES);
    System.out.println("SUCCESS, totalTable="+totalTable.sum());
    System.out.println("SUCCESS, totalRecTable="+totalRecTable.sum());
    }

    private void handleDb(String db, List<String> tables) throws Exception {
        int count=0;
        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+db+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        String destDir = MainHelper.OUTPUT_DIR+"/"+db+"/";

        for(String table : tables){
            String check = String.format("select * from `%s`.`%s` limit 1;", db, table);
            System.out.println(count++ + "\t"+check);
            totalTable.increment();
            Statement stmt = connection.createStatement();
            ResultSet checkResultSet = stmt.executeQuery(check);
            String orderBy = "";
            if(checkResultSet.next()){
                String firstCol = checkResultSet.getMetaData().getColumnName(1);
                orderBy = "order by `"+firstCol+"` desc";
            }else{
                continue;
            }
            totalRecTable.increment();
            String query = String.format("select * from `%s`.`%s` %s limit %s;", db, table, orderBy, num);
//                System.out.println(query);
            ResultSet resultSet = stmt.executeQuery(query);
            String insertSql = DbUtils.rsToInsert(resultSet);
            checkResultSet.close();
            resultSet.close();
            stmt.close();
//        System.out.println("\twrite "+table);
            Files.write(Paths.get(destDir+table+".sql"),
                    Arrays.asList(insertSql),
                    StandardOpenOption.CREATE,
                    StandardOpenOption.DSYNC);
//                System.out.println("\twrite end "+table);
//                Thread.sleep(sleepSec*1000);
//		  System.out.println(insertSql);
        }
        connection.close();
    }

    @Before
    public void before() throws Exception {
        // 懒猪 猪耳朵 每日说 贝小集 懒猪同城 懒猪探店
        if("每日说".equals(project)){
            userDailyDb();
        }else if("贝小集".equals(project)){
            userBxjDb();
        }else{
            throw new RuntimeException("project 参数不正确");
        }

    }

    private void userDailyDb(){
        username = "dev_root";
        password = "Dev@20220621!";
        dbList = Arrays.asList(("xqxc_admin,xqxc_goods,xqxc_rider," +
                "xqxc_advert,xqxc_hotel,xqxc_settle,xqxc_center_order," +
                "xqxc_marketing,xqxc_show,xqxc_charge,xqxc_merchant," +
                "xqxc_user,xqxc_dance,xqxc_order,xqxc_user2," +
                "xqxc_earnings,xqxc_pay,xqxc_xiudou,xqxc_flink," +
                "xqxc_quartz").split(","));
        dbname = "xqxc_admin";
        ipPort = "rm-bp1qvq6wr6hn6gidlpm.mysql.rds.aliyuncs.com:3306/";

    }

    private void userBxjDb(){
        username = "dev_admin";
        password = "Dev+2019@xqxc";
        dbList = Arrays.asList(("xqxc_admin,xqxc_advert," +
                "xqxc_collector,xqxc_dance,xqxc_earnings," +
                "xqxc_flink,xqxc_goods,xqxc_hotel," +
                "xqxc_marketing,xqxc_merchant,xqxc_order," +
                "xqxc_pay,xqxc_quartz,xqxc_report," +
                "xqxc_rider,xqxc_settle,xqxc_show," +
                "xqxc_user,xqxc_xiudou").split(","));
        dbname = "xqxc_admin";
        ipPort = "rm-bp16tdzf48mvnql762o.mysql.rds.aliyuncs.com:3306/";

    }


}
